﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace syc.Juece.Report
{
    public partial class 月原辅材料收发平衡表 : System.Web.UI.Page
    {
        protected HSSFWorkbook hssfworkbook;
        protected double 油管光管期初库存;
        protected double 油管光管本期入库;
        protected double 油管光管本期出库;
        protected double 油管光管期末库存;
        protected int year;
        protected int month;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                this.Month.SelectedValue = DateTime.Today.Month.ToString();
                this.Year0.Text = DateTime.Today.Year.ToString();
            }
            Button_Query_Click(null, null);
        }
        protected void WriteToFile()
        {
            DateTime d;

            d = DateTime.Today;

            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + d.ToShortDateString() + ".xls\"");
            hssfworkbook.Write(Response.OutputStream);
        }
        protected void InitializeWorkbook()
        {

            FileStream file = new FileStream(HttpContext.Current.Server.MapPath("/JueCe/模板/月经济目标责任考核表.xls"), FileMode.Open, FileAccess.Read);

            hssfworkbook = new HSSFWorkbook(file);

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "陕西延长石油材料有限公司";
            hssfworkbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "报表";
            si.Title = "陕西延长石油材料公司月份入库量统计快报";
            hssfworkbook.SummaryInformation = si;
        }
        protected void Button_Query_Click(object sender, EventArgs e)
        {
            year = int.Parse(this.Year0.Text);
            month  = int.Parse(this.Month.Text);

            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            //获得年计划
            Cmd.CommandText = @"
            
           select isnull(一分厂产品2,0) as 一分厂,isnull(二分厂产品1,0) as 二分厂油管,isnull(二分厂产品2,0) as 二分厂套管,isnull(五分厂产品1,0) as 五分厂,isnull(三分厂产品1,0) as 月油管接箍,isnull(三分厂产品2,0) as 月套管接箍 from 生产计划 where 年度='" + year + "' and 月份=0";
            //SqlDataReader Reader = Cmd.ExecuteReader();
            //if (Reader.Read())
            //{

            //    一分厂年计划 = Reader.GetDouble(0);
            //    二分厂油管年计划 = Reader.GetDouble(1);
            //    二分厂套管年计划 = Reader.GetDouble(2);
            //    五分厂年计划 = Reader.GetDouble(3);
            //    nyjiegujihua = Reader.GetDouble(4);
            //    ntjiegujihua = Reader.GetDouble(5);
            //}
            //Reader.Close();
            ////原料年计划
            //Cmd.CommandText = @"select isnull(sum(油管),0) as 油管光管,isnull(sum(套管),0) as 套管光管,isnull(sum(油管接箍),0) as 油管接箍光管,isnull(sum(套管接箍),0) as 套管接箍光管,isnull(sum(成品套管接箍),0) as 成品套管接箍 from 原料采购计划 where 年度='" + year + "' and 季度=0";
            //Reader = Cmd.ExecuteReader();
            //if (Reader.Read())
            //{
            //    油管光管年计划 = Reader.GetDouble(0);
            //    套管光管年计划 = Reader.GetDouble(1);
            //    油管接箍光管年计划 = Reader.GetDouble(2);
            //    套管接箍光管年计划 = Reader.GetDouble(3);
            //    成品套管接箍年计划 = Reader.GetDouble(4);
            //}
            //Reader.Close();



            Conn.Close();
            Reader.Dispose();
            Cmd.Dispose();
            Conn.Dispose();
        }

        protected void Button_Save_Click(object sender, EventArgs e)
        {
            InitializeWorkbook();
            HSSFSheet sheet1 = hssfworkbook.GetSheet("月经济目标责任考核表");
            Button_Query_Click(null, null);
            sheet1.GetRow(0).GetCell(0).SetCellValue(this.Month.SelectedItem.Text + "月份经济目标责任考核表");

           
            sheet1.GetRow(17).GetCell(6).SetCellValue("填报日期:" + DateTime.Now.ToString("yyyy-MM-dd"));
            WriteToFile();
            Response.End();
        }

    }
}
